package cn.edu.ecut.se2024.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.ecut.se2024.User.waresentry;


public class waresentryService {
	String dbURL = "jdbc:sqlserver://localhost:1433;DatabaseName=Stock;encrypt=true;trustServerCertificate=true";
    String userName = "sa";
    String userPwd = "123456";
    Connection con;
	public waresentryService() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			System.out.println("驱动加载成功!");
			
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			con = DriverManager.getConnection(dbURL, userName, userPwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("数据库连接成功");
	}
	public  List<waresentry> queryAllwaresentry(){
		List<waresentry> wares=new ArrayList<>();
		PreparedStatement psmt=null;
		ResultSet rs=null;	
		String sql="select * from Waresentrty";
		try {
			psmt=con.prepareStatement(sql);
			rs=psmt.executeQuery();
			while(rs.next()) {
				int no =rs.getInt("Number");
				String supplier=rs.getString("Supplier");
				String id=rs.getString("PID");
				String name=rs.getString("Pname");
				int number=rs.getInt("Pquantity");
				int price=rs.getInt("Pprice");
				waresentry ware=new waresentry(no);
				wares.add(ware);
			}
		}
		 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return wares;
	}
	public waresentry querywaresentryBysno(int number) {
		PreparedStatement psmt=null;
		ResultSet rs=null;	
		String sql="select * from Waresentry where number=?";
		try {
			psmt=con.prepareStatement(sql);
			psmt.setInt(1, number);
			rs=psmt.executeQuery();
			if(rs.next()) {
				int no =rs.getInt("Number");
				String supplier=rs.getString("Supplier");
				String id=rs.getString("PID");
				String name=rs.getString("Pname");
				int num=rs.getInt("Pquantity");
				int price=rs.getInt("Pprice");
				waresentry ware=new waresentry(no,supplier,id,name,num,price);
				return ware;
				
			}else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean isExist(int sno) {
		return (querywaresentryBysno(sno)==null)?false:true;
	}
	public boolean addpurchase(waresentry ware) {
		PreparedStatement psmt=null;
		String sql="insert into Waresentry values(?,?,?,?,?,?)";
		try {
			psmt=con.prepareStatement(sql);
			psmt.setInt(1,ware.getNumber());
			psmt.setString(2,ware.getSupplier());
			psmt.setString(3,ware.getPID());
			psmt.setString(4,ware.getPname());
			psmt.setInt(5,ware.getPquantity());
			psmt.setInt(6,ware.getPprice());
			int count=psmt.executeUpdate();
			if(count>0) return true;
			else return false;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;	
	}
	public boolean deletewaresentryBysno(int sno) {
		PreparedStatement psmt=null;
		String sql="delete from Waresentry where sno=?";
		try {
			psmt=con.prepareStatement(sql);
			psmt.setInt(1, sno);
			int count=psmt.executeUpdate();
			if(count>0) return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;	
	}
}